﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;


namespace IHISCETeim.Web.Export.FXModel
{
public partial class ReportExpiryNumSum : ExportPageBase
{
    protected override string xlsTemplateFileName
    {
        get
        {
            return "经销商逾期单数汇总表.xls";
        }
    }
    protected override string SheetName
    {
        get
        {
            return "Sheet1";
        }
    }

    protected int UserId
    {
        get
        {
            return GetRequestValue<int>("u", 0);
        }
    }

    protected string LeaseCompany
    {
        get
        {
            return GetRequestValue<string>("LeaseCompany", "");
        }
    }

    protected DataTable sumdt = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
        string wheresql = "";
        if (UserId > 0)
        {
            string agentsqltemp = SAreaManagerAgentController.GetMyAgentSqlWhere();
            wheresql = agentsqltemp;
        }
        DataTableCollection dts = FXFinanceLeaseController.ReportGetJXSYQFZ(wheresql, LeaseCompany, " 剩余单数 desc");
        DataTable exportDT = dts[0];

        InitializeWorkbook();

        ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
        //写标题
        HSSFRow rowTitle = (HSSFRow)sheet1.CreateRow(0);
        string DTTitle = "经销商逾期单数汇总表";
        DTTitle = DTTitle + (string.IsNullOrEmpty(LeaseCompany) ? "" : "(" + LeaseCompany + ")");
        ICell cell = rowTitle.CreateCell(0);
        cell.SetCellValue(DTTitle);
        ICellStyle style = hssfworkbook.CreateCellStyle();
        style.Alignment = HorizontalAlignment.CENTER;
        IFont font = hssfworkbook.CreateFont();
        font.FontHeight = 20 * 20;
        style.SetFont(font);
        cell.CellStyle = style;

        sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 14));

        //FOR写入
        int rowindexBase = 2;
        int rowindex = 0;
        int rowReal = rowindex + rowindexBase;
        foreach (DataRow dr in exportDT.Rows)
        {


            HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
            // row1.CreateCell(0).SetCellValue(1);
            row1.CreateCell(0).SetCellValue(dr["经销商"].ToString());
            row1.CreateCell(1).SetCellValue(Convert.ToInt32(dr["总单数"]));
            row1.CreateCell(2).SetCellValue(Convert.ToInt32(dr["结清单数"]));
            row1.CreateCell(3).SetCellValue(Convert.ToInt32(dr["剩余单数"]));
            row1.CreateCell(4).SetCellValue(Convert.ToDouble(dr["未结清用户月租金"]));
            row1.CreateCell(5).SetCellValue(Convert.ToDouble(dr["剩余总租金"]));
            row1.CreateCell(6).SetCellValue(Convert.ToInt32(dr["逾期1期"]));
            row1.CreateCell(7).SetCellValue(Convert.ToInt32(dr["逾期2期"]));
            row1.CreateCell(8).SetCellValue(Convert.ToInt32(dr["逾期3期"]));
            row1.CreateCell(9).SetCellValue(Convert.ToInt32(dr["逾期4期"]));
            row1.CreateCell(10).SetCellValue(Convert.ToInt32(dr["逾期5期"]));
            row1.CreateCell(11).SetCellValue(Convert.ToInt32(dr["逾期6期"]));
            row1.CreateCell(12).SetCellValue(Convert.ToInt32(dr["逾期7期"]));
            row1.CreateCell(13).SetCellValue(Convert.ToInt32(dr["逾期8期"]));
            row1.CreateCell(14).SetCellValue(Convert.ToDouble(dr["逾期总金额"]));
            row1.CreateCell(15).SetCellValue(GetPer(Convert.ToDouble(dr["逾期总金额"]), Convert.ToDouble(dr["剩余总租金"])).ToString());//逾期率%=逾期总金额/剩余总租金
            row1.CreateCell(16).SetCellValue(Convert.ToInt32(dr["逾期3期"]) + Convert.ToInt32(dr["逾期4期"]) + Convert.ToInt32(dr["逾期5期"]) + Convert.ToInt32(dr["逾期6期"]) + Convert.ToInt32(dr["逾期7期"]) + Convert.ToInt32(dr["逾期8期"]));//3期以上单数
            row1.CreateCell(17).SetCellValue(Convert.ToDouble(dr["当年合同总数"]));
            row1.CreateCell(18).SetCellValue(Convert.ToDouble(dr["当年剩余总租金"]));
            row1.CreateCell(19).SetCellValue(Convert.ToDouble(dr["当年前6期逾期单数"]));
            row1.CreateCell(20).SetCellValue(Convert.ToDouble(dr["当年前6期剩余金额"]));
            row1.CreateCell(21).SetCellValue(GetPer(Convert.ToDouble(dr["当年前6期剩余金额"]), Convert.ToDouble(dr["当年剩余总租金"])).ToString());//前6期逾期比率=当年前6期逾期金额/当年剩余总租金
            rowindex++;
            rowReal = rowindex + rowindexBase;
        }

        //合计行
        HSSFRow rowSum = (HSSFRow)sheet1.CreateRow(rowReal);
        rowSum.CreateCell(0).SetCellValue("合计:");
        rowSum.CreateCell(1).SetCellFormula("sum(B" + (rowindexBase + 1).ToString() + ":B" + (rowReal).ToString() + ")");
        rowSum.CreateCell(2).SetCellFormula("sum(C" + (rowindexBase + 1).ToString() + ":C" + (rowReal).ToString() + ")");
        rowSum.CreateCell(3).SetCellFormula("sum(D" + (rowindexBase + 1).ToString() + ":D" + (rowReal).ToString() + ")");
        rowSum.CreateCell(4).SetCellFormula("sum(E" + (rowindexBase + 1).ToString() + ":E" + (rowReal).ToString() + ")");
        rowSum.CreateCell(5).SetCellFormula("sum(F" + (rowindexBase + 1).ToString() + ":F" + (rowReal).ToString() + ")");
        rowSum.CreateCell(6).SetCellFormula("sum(G" + (rowindexBase + 1).ToString() + ":G" + (rowReal).ToString() + ")");
        rowSum.CreateCell(7).SetCellFormula("sum(H" + (rowindexBase + 1).ToString() + ":H" + (rowReal).ToString() + ")");
        rowSum.CreateCell(8).SetCellFormula("sum(I" + (rowindexBase + 1).ToString() + ":I" + (rowReal).ToString() + ")");
        rowSum.CreateCell(9).SetCellFormula("sum(J" + (rowindexBase + 1).ToString() + ":J" + (rowReal).ToString() + ")");
        rowSum.CreateCell(10).SetCellFormula("sum(K" + (rowindexBase + 1).ToString() + ":K" + (rowReal).ToString() + ")");
        rowSum.CreateCell(11).SetCellFormula("sum(L" + (rowindexBase + 1).ToString() + ":L" + (rowReal).ToString() + ")");
        rowSum.CreateCell(12).SetCellFormula("sum(M" + (rowindexBase + 1).ToString() + ":M" + (rowReal).ToString() + ")");
        rowSum.CreateCell(13).SetCellFormula("sum(N" + (rowindexBase + 1).ToString() + ":N" + (rowReal).ToString() + ")");
        rowSum.CreateCell(14).SetCellFormula("sum(O" + (rowindexBase + 1).ToString() + ":O" + (rowReal).ToString() + ")");
        //Force excel to recalculate all the formula while open

        sheet1.ForceFormulaRecalculation = true;

        WriteToFile();
    }

    protected object GetPer(object 逾期总金额, object 剩余总租金)
    {
        //逾期总金额 /剩余总租金

        if (Convert.ToDecimal(逾期总金额) == 0)
            return 0;

        return ( (Convert.ToDecimal(逾期总金额) / Convert.ToDecimal(剩余总租金))*100).ToString("F2");

    }
}
}